#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
@Project ：pyqt5_device 
@File    ：mydb.py
@IDE     ：PyCharm 
@Author  ：Toby
@Date    ：2023/8/15 21:07 
'''

import pymysql
from pymysql.constants import CLIENT
import json
from jsonpath import jsonpath

class MySqlDb:
    # 连接数据库
    def __init__(self):
        try:
            with open('./config.json', 'r', encoding='utf8')as fp:
                json_data = fp.read()
                json_data = json.loads(json_data)
                self.host = jsonpath(json_data, "$.testsss_db.host")
                self.port = jsonpath(json_data, "$.testsss_db.port")
                self.user = jsonpath(json_data, "$.testsss_db.user")
                self.passwd = jsonpath(json_data, "$.testsss_db.passwd")
                self.db = jsonpath(json_data, "$.testsss_db.db")
                print(f"当前数据库的配置:地址：{self.host} :{self.port}")

                # 打开数据库连接
                self.conn = pymysql.connect(host=self.host[0],
                                            port=self.port[0],
                                            user=self.user[0],
                                            passwd=self.passwd[0],
                                            db=self.db[0],
                                            charset='utf8',
                                            client_flag=CLIENT.MULTI_STATEMENTS)  # 添加client_flag参数)
                print(self.conn)



        except Exception as e:
            print(f"database connect except :{e}")


    # sql语句执行,返回字典类型
    def select_sql(self, sql_string):
            # 获取游标
        self.cursor = self.conn.cursor()
        self._query = sql_string
        print('查询语句:{}'.format(self._query))
        try:
            self.cursor.execute(query=self._query)
            coloumns = [row[0] for row in self.cursor.description]
            result = [[str(item) for item in row] for row in self.cursor.fetchall()]
            return [dict(zip(coloumns, row)) for row in result]
        except:
            self.conn.rollback()
            print('执行查询的失败 ')
            return None

    # 返回tuple类型  # 可以用来执行update等语句
    def sql_run(self, sql_string):
        self.cursor = self.conn.cursor()

        self.query = sql_string
        print("执行SQL语句:{}".format(self.query))

        try:
            # 执行 ,提交 ,返回执行结果
            self.cursor.execute(query=self.query)
            self.conn.commit()  # 需要使用commint()才能操作提交成功
        except Exception as e:
            self.conn.rollback()
            print('执行sql失败:', e)
            return None

    # 断开数据库
    def close(self):
        self.cursor.close()
        self.conn.close()
        print('mysql closed')


if __name__ == '__main__':
    mydb = MySqlDb()

    sql = 'select * from t_charge_pile where mngId="4ebdcda13dba" limit 1;'
    ress = mydb.select_sql(sql)
    print(ress[0])
    print(ress[0]['boxCode'])

    sql = 'SELECT * FROM t_charge_user WHERE mobile ="13189593801";  '
    res = mydb.select_sql(sql)
    print(res[0]['userId'])

    sql = 'SELECT * FROM t_charge_deal WHERE userId =' + res[0]['userId'] + '  ORDER BY dealId DESC limit 1;'
    res = mydb.select_sql(sql)
    print(res[0]['dealId'])

    mydb.close()
